from selenium import webdriver
# from selenium.webdriver.chrome.service import Service
import time
from selenium.webdriver.common.by import By
import ddddocr
import threading
import queue
import os
import xlwt
from io import BytesIO
# wd = webdriver.Chrome(service=Service(r'C:\Users\27376\Desktop\class\chromedriver_win32\chromedriver.exe'))


import pymysql

# 建立数据库连接



def get_class(data):
    chrome_options = webdriver.ChromeOptions()
    chrome_options.add_argument("disable-cache")
    chrome_options.add_experimental_option('excludeSwitches', ['enable-logging'])
    wd = webdriver.Chrome(options=chrome_options)
    # 无界面模式仍有bug 目前 解决方向有 分辨率 问题 过低会导致元素重叠
    wd.implicitly_wait(10)
    user_names = data['username']
    passwords = data['password']
    # 调用WebDriver 对象的get方法 可以让浏览器打开指定网址
    wd.get('https://iam.pt.ouchn.cn/am/UI/Login')
    # 根据id选择元素，返回的就是该元素对应的WebElement对象
    user_name = wd.find_element(By.ID, 'loginName')
    password = wd.find_element(By.ID, 'password')
    login = wd.find_element(By.ID, 'button')
    img_code = wd.find_element(By.ID, 'validateCode')
    img = wd.find_element(By.ID, 'kaptchaImage')

    # 获取图片验证码 图片 并给与名字 同名覆盖 不会占用太多资源

    current_dir = os.getcwd()

    img_name = str(current_dir + f"\{str(user_names)}.png")

    with open(img_name,'wb') as f:
        f.write(img.screenshot_as_png)

    # 图片验证实例
    ocr = ddddocr.DdddOcr()
    with open(img_name, 'rb') as f:

        img_bytes = f.read()
    code = ocr.classification(img_bytes)

    user_name.send_keys(user_names)
    password.send_keys(passwords)

    img_code.send_keys(code)
    # 点击事件
    login.click()

    wd.get('https://menhu.pt.ouchn.cn/site/ouchnPc/index')
    time.sleep(5)

    name = ''
    try:
        name = wd.find_element(By.CSS_SELECTOR,'body > div:nth-child(1) > div.ouchnPc_index > div.ouchnPc_index_main > div.ouchnPc_index_info.w > div.swiper-wrap > div.swiper-container.swiper-container-horizontal > div.swiper-wrapper > div.swiper-slide.ouchnPc_index_info_left_teacher.swiper-slide-active > div > div.photo_name.student_height > div > span')
        name = name.get_attribute('innerHTML')
    except Exception as e:
        sql = "INSERT INTO class_model (stu_num, username, class_name) VALUES (%s, %s, %s)"
        # insert into asd.`test_table` (`username`, `username11`, `username22`) values ('顾弘文', '萧雨泽', '侯立诚');
        val = (user_names, passwords,'出现错误')
        cursor.execute(sql, val)
        # 提交修改
        conn.commit()
        current_dir = str(current_dir + f"\{str(user_names)}.png")
        if os.path.exists(current_dir):  # 如果该路径存在
            os.remove(current_dir)  # 就删除这个文件
        else:
            pass
        return

    now_total_element = wd.find_element(By.CSS_SELECTOR,
                                        "body > div:nth-child(1) > div.ouchnPc_index > div.ouchnPc_index_main > div.ouchnPc_index_course.w > div > span.el-pagination__total")
    now_total = now_total_element.get_attribute("innerHTML").split(" ")[1]
    if now_total != 0:
        if int(now_total) < 6:
            for i in range(1, int(now_total) + 1):
                class_name = wd.find_element(By.CSS_SELECTOR,
                                             'body > div:nth-child(1) > div.ouchnPc_index > div.ouchnPc_index_main > div.ouchnPc_index_course.w > ul > li:nth-child(%s) > div > div > p' % (
                                                 i))

                sql = "INSERT INTO class_model (stu_num, username, class_name) VALUES (%s, %s, %s)"
                # insert into asd.`test_table` (`username`, `username11`, `username22`) values ('顾弘文', '萧雨泽', '侯立诚');
                val = (name, user_names, class_name.get_attribute('innerHTML').split('\n')[1].split(' ')[-1])
                cursor.execute(sql, val)
                # 提交修改
                conn.commit()
        else:
            page_count = int(now_total) // 6
            last_count = int(now_total) - page_count * 6
            for j in range(page_count):
                for i in range(1, 7):
                    class_name = wd.find_element(By.CSS_SELECTOR,
                                                 'body > div:nth-child(1) > div.ouchnPc_index > div.ouchnPc_index_main > div.ouchnPc_index_course.w > ul > li:nth-child(%s) > div > div > p' % i)
                    sql = "INSERT INTO class_model (stu_num, username, class_name) VALUES (%s, %s, %s)"
                    # insert into asd.`test_table` (`username`, `username11`, `username22`) values ('顾弘文', '萧雨泽', '侯立诚');
                    val = (name, user_names, class_name.get_attribute('innerHTML').split('\n')[1].split(' ')[-1])
                    cursor.execute(sql, val)
                    # 提交修改
                    conn.commit()
                aaa = wd.find_element(By.CSS_SELECTOR,
                                      'body > div:nth-child(1) > div.ouchnPc_index > div.ouchnPc_index_main > div.ouchnPc_index_course.w > div > button.btn-next')
                wd.execute_script("arguments[0].click();", aaa)
                time.sleep(1)
            for i in range(1, last_count + 1):
                class_name = wd.find_element(By.CSS_SELECTOR,
                                             'body > div:nth-child(1) > div.ouchnPc_index > div.ouchnPc_index_main > div.ouchnPc_index_course.w > ul > li:nth-child(%s) > div > div > p' % i)
                sql = "INSERT INTO class_model (stu_num, username, class_name) VALUES (%s, %s, %s)"
                # insert into asd.`test_table` (`username`, `username11`, `username22`) values ('顾弘文', '萧雨泽', '侯立诚');
                val = (name, user_names, class_name.get_attribute('innerHTML').split('\n')[1].split(' ')[-1])
                cursor.execute(sql, val)
                # 提交修改
                conn.commit()

    element = wd.find_element(By.CSS_SELECTOR,
                              'body > div:nth-child(1) > div.ouchnPc_index > div.ouchnPc_index_main > div.ouchnPc_index_course.w > section > span:nth-child(2)')
    wd.execute_script("arguments[0].click();", element)

    time.sleep(1)
    now_total_element = wd.find_element(By.CSS_SELECTOR,
                                        "body > div:nth-child(1) > div.ouchnPc_index > div.ouchnPc_index_main > div.ouchnPc_index_course.w > div > span.el-pagination__total")  # 总共有几条数据
    now_total = now_total_element.get_attribute("innerHTML").split(" ")[1]
    if now_total != 0:
        if int(now_total) < 6:
            for i in range(1, int(now_total) + 1):
                class_name = wd.find_element(By.CSS_SELECTOR,
                                             'body > div:nth-child(1) > div.ouchnPc_index > div.ouchnPc_index_main > div.ouchnPc_index_course.w > ul > li:nth-child(%s) > div > div > p' % (
                                                 i))
                sql = "INSERT INTO class_model (stu_num, username, class_name) VALUES (%s, %s, %s)"
                # insert into asd.`test_table` (`username`, `username11`, `username22`) values ('顾弘文', '萧雨泽', '侯立诚');
                val = (name, user_names, class_name.get_attribute('innerHTML').split('\n')[1].split(' ')[-1])
                cursor.execute(sql, val)
                # 提交修改
                conn.commit()
        else:
            page_count = int(now_total) // 6
            last_count = int(now_total) - page_count * 6
            for j in range(page_count):
                for i in range(1, 7):
                    class_name = wd.find_element(By.CSS_SELECTOR,
                                                 'body > div:nth-child(1) > div.ouchnPc_index > div.ouchnPc_index_main > div.ouchnPc_index_course.w > ul > li:nth-child(%s) > div > div > p' % i)
                    sql = "INSERT INTO class_model (stu_num, username, class_name) VALUES (%s, %s, %s)"
                    # insert into asd.`test_table` (`username`, `username11`, `username22`) values ('顾弘文', '萧雨泽', '侯立诚');
                    val = (name, user_names, class_name.get_attribute('innerHTML').split('\n')[1].split(' ')[-1])
                    cursor.execute(sql, val)
                    # 提交修改
                    conn.commit()
                bbb = wd.find_element(By.CSS_SELECTOR,
                                      'body > div:nth-child(1) > div.ouchnPc_index > div.ouchnPc_index_main > div.ouchnPc_index_course.w > div > button.btn-next')
                wd.execute_script("arguments[0].click();", bbb)
                time.sleep(1)
            for i in range(1, last_count + 1):
                class_name = wd.find_element(By.CSS_SELECTOR,
                                             'body > div:nth-child(1) > div.ouchnPc_index > div.ouchnPc_index_main > div.ouchnPc_index_course.w > ul > li:nth-child(%s) > div > div > p' % i)
                sql = "INSERT INTO class_model (stu_num, username, class_name) VALUES (%s, %s, %s)"
                # insert into asd.`test_table` (`username`, `username11`, `username22`) values ('顾弘文', '萧雨泽', '侯立诚');
                val = (name, user_names, class_name.get_attribute('innerHTML').split('\n')[1].split(' ')[-1])
                cursor.execute(sql, val)
                # 提交修改
                conn.commit()
    current_dir = str(current_dir + f"\{str(user_names)}.png")
    if os.path.exists(current_dir):  # 如果该路径存在
        os.remove(current_dir)  # 就删除这个文件
    else:
        pass
    return


def process_thread(data_list):
    while True:
        try:
            # 从线程安全的队列中读取数据
            data = data_list.pop(0)
        except IndexError:  # 如果队列为空，则跳出循环
            break
        else:
            # 进行数据处理
            result_queue.put(get_class(data))


def threading_start(data):
    thread_list = []
    for i in range(3):
        t = threading.Thread(target=process_thread, args=[data])
        thread_list.append(t)
        t.start()
    # 等待所有线程完成
    for t in thread_list:
        t.join()
    result_list = []
    while not result_queue.empty():
        result_list.append(result_queue.get())
    return result_list


def get_excel(text, name):
    output = BytesIO()
    workbook = xlwt.Workbook()
    sheet = workbook.add_sheet('Users')

    # 将标题行写入工作表
    header_row = ['姓名', '学号', '课程名称']
    for i in range(len(header_row)):
        sheet.write(0, i, header_row[i])

    # 将数据行写入工作表
    row = 1
    for i in text:
        for row_data in i:
            for col in range(len(row_data)):
                sheet.write(row, col, str(row_data[col]))
            row += 1

    # 保存并关闭 Excel 工作簿
    workbook.save(output)
    output.seek(0)
    # 创建桌面的文件路径
    file_path = os.path.join(os.path.expanduser('~'), 'Desktop', f'{name}.xls')

    # 将 Excel 文件写入桌面
    with open(file_path, 'wb') as f:
        f.write(output.getvalue())

    return


if __name__ == '__main__':
    conn = pymysql.connect(
        host='localhost',
        port=3306,
        user='root',
        password='123456',
        db='spiders',
        charset='utf8mb4'
    )

    # 获取游标
    cursor = conn.cursor()

    # 执行SQL语句
    sql = "SELECT * FROM user_model"
    cursor.execute(sql)

    # 获取所有数据
    rows = cursor.fetchall()

    # 将数据保存到列表
    data_list = [list(row) for row in rows]
    lis = []
    for i in data_list:
        lis.append({
            'username':i[1],
            'password':i[2]
        })

    result_queue = queue.Queue()
    aa = threading_start(lis)
    # 关闭游标和数据库连接
    cursor.close()
    conn.close()